﻿using System;
using System.Data;
using System.Data.Common;
using GPSTrackingServerDB.Data;
using GPSTrackingServerDB.Data.SqlClient;
using GPSTrackingServerDB.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Collections.Specialized;
using System.Data.SqlClient;

namespace GPSTrackingServerDBCustom.Data
{
    public class DataRepository
    {
        public static string _connectionString = GPSTrackingServerDB.Data.DataRepository.ConnectionStrings["netTiersConnectionString"].ConnectionString;

        public class ImeiProvider
        {
            public static TList<Imei> GetImeiByUsername(string username)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Imei_CustomGetByUsername", true);
                
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                IDataReader reader =  Utility.ExecuteReader(database, commandWrapper);

                TList<Imei> imeiEntities = new TList<Imei>();

                
                while (reader.Read())
                {
                    Imei entity = new Imei();
                    entity.Imei = reader.GetString(0);
                    entity.Name = reader.GetString(1);
                    entity.SimNumber = reader.GetDecimal(2);
                    entity.Description = reader.GetString(3);
                    entity.ImeiTypeId = reader.GetInt32(4);
                    entity.GpsModelId = reader.GetInt16(5);                    

                    imeiEntities.Add(entity);
                }
                
                

                return imeiEntities;

            }

            public static DataTable GetDataTableImeiByUsername(string username)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Imei_CustomGetByUsername", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);                

                return ds.Tables[0];
            }

            public static DataTable GetAllImeiWithinUser()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Imei_CustomGetAllImeiWithinUser", true);                

                DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);                

                return ds.Tables[0];
            }

            
        }

        public class GeoFenceProvider 
        {
            public static void DeleteGeoFenceCompletely(int geoFenceID, string imei)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GeoFence_DeleteCompletely", true);
                
                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int16, geoFenceID);
                database.AddInParameter(commandWrapper, "@Imei", DbType.String, imei);                

                database.ExecuteNonQuery(commandWrapper);
            }
        }

        public class TrackDataLastLocationProvider
        {
            public static void UpdateOrInsert(TrackDataLastLocation TrackDataLastLocationEntity)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Track_Data_Last_Location_Update_Or_Insert", true);

                database.AddInParameter(commandWrapper, "@Imei", DbType.String,TrackDataLastLocationEntity.Imei);
                database.AddInParameter(commandWrapper, "@OriginalImei", DbType.String, TrackDataLastLocationEntity.Imei);
                database.AddInParameter(commandWrapper, "@DateTime ", DbType.DateTime,TrackDataLastLocationEntity.DateTime );
                database.AddInParameter(commandWrapper, "@Latitude", DbType.Double, TrackDataLastLocationEntity.Latitude);
                database.AddInParameter(commandWrapper, "@Longitude", DbType.Double, TrackDataLastLocationEntity.Longitude);
                database.AddInParameter(commandWrapper, "@Heading", DbType.Double, TrackDataLastLocationEntity.Heading);
                database.AddInParameter(commandWrapper, "@Speed", DbType.Double, TrackDataLastLocationEntity.Speed);
                database.AddInParameter(commandWrapper, "@MainPowerCut ", DbType.Boolean, TrackDataLastLocationEntity.MainPowerCut);
                database.AddInParameter(commandWrapper, "@Acc ", DbType.Boolean, TrackDataLastLocationEntity.Acc );
                database.AddInParameter(commandWrapper, "@Input1 ", DbType.Boolean, TrackDataLastLocationEntity.Input1);
                database.AddInParameter(commandWrapper, "@Input2 ", DbType.Boolean, TrackDataLastLocationEntity.Input2);
                database.AddInParameter(commandWrapper, "@OilCuttingAlarm ", DbType.Boolean, TrackDataLastLocationEntity.OilCuttingAlarm);
                //database.AddInParameter(commandWrapper, "@PoiAlarm ", DbType.Boolean, TrackDataLastLocationEntity.POIAlarm);
                database.AddInParameter(commandWrapper, "@InflameAlarm ", DbType.Boolean, TrackDataLastLocationEntity.InflameAlarm);
                database.AddInParameter(commandWrapper, "@OverspeedAlarm ", DbType.Boolean, TrackDataLastLocationEntity.OverspeedAlarm);
                database.AddInParameter(commandWrapper, "@CarParkAlarm ", DbType.Boolean, TrackDataLastLocationEntity.CarParkAlarm);
                database.AddInParameter(commandWrapper, "@GeoferenceExitAlarm ", DbType.Boolean, TrackDataLastLocationEntity.GeoferenceExitAlarm);
                database.AddInParameter(commandWrapper, "@GeoferenceEnterAlarm ", DbType.Boolean, TrackDataLastLocationEntity.GeoferenceEnterAlarm);
                database.AddInParameter(commandWrapper, "@OutOfRoadLineAlarm ", DbType.Boolean, TrackDataLastLocationEntity.OutOfRoadLineAlarm);
                database.AddInParameter(commandWrapper, "@DisplacementAlarm ", DbType.Boolean, TrackDataLastLocationEntity.DisplacementAlarm);
                database.AddInParameter(commandWrapper, "@OverTimeDrivingAlarm ", DbType.Boolean, TrackDataLastLocationEntity.OverTimeDrivingAlarm);
                //database.AddInParameter(commandWrapper, "@SosAlarm ", DbType.Boolean, TrackDataLastLocationEntity.SOSAlarm);

                Utility.ExecuteNonQuery(database, commandWrapper);
            }

            public static string GetImeiTrackDataLastLocationByUser(string username)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
             
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.TrackDataLastLocation_GetImeiByUser", true);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetGroupsTrackDataLastLocationByUser(string username)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.TrackDataLastLocation_GetGroupsByUser", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    dsLastLocation = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsLastLocation.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class GeoFenceByImeiProvider
        { 
            public static bool ExistGeoFenceByImeiAndDescription(string imei, string description)
            {
                int total = 0;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GeoFenceByImei_CountByDescriptionAndImei", true);

                database.AddInParameter(commandWrapper, "@Imei", DbType.String, imei);
                database.AddInParameter(commandWrapper, "@Description", DbType.String, description);

                try
                {
                    total = (int)Utility.ExecuteScalar(database, commandWrapper);

                    if (total > 0)
                        return true;
                    return false;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class TrackDataProvider
        {
            public static string GetAllByImeiAndDate2(string imei, DateTime date)
            { 
                
                DataSet dsTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Track_Data_GetAllByImeiAndDate", true);
                database.AddInParameter(commandWrapper, "@Imei", DbType.String, imei);
                database.AddInParameter(commandWrapper, "@DateTime", DbType.DateTime, date);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static StringCollection GetAllByImeiAndDate(string imei, DateTime date)
            {

                DataSet dsTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                StringCollection imeiInfoList = new StringCollection();

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Track_Data_GetAllByImeiAndDate", true);
                database.AddInParameter(commandWrapper, "@Imei", DbType.String, imei);
                database.AddInParameter(commandWrapper, "@DateTime", DbType.DateTime, date);

                try
                {
                    dsTrackData = Utility.ExecuteDataSet(database, commandWrapper);

                    for (int i=0; i < dsTrackData.Tables[0].Rows.Count; i++)
                    {
                        if (imeiInfoList.Count == 0)
                        {
                            imeiInfoList.Add(   dsTrackData.Tables[0].Rows[i]["Imei"].ToString()    + "&" + 
                                                dsTrackData.Tables[0].Rows[i]["Image"].ToString());
                        }

                        imeiInfoList.Add(   dsTrackData.Tables[0].Rows[i]["Hour"].ToString()        + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Speed"].ToString()       + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Latitude"].ToString()    + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Longitude"].ToString());
                    }

                    return imeiInfoList;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class GeoFenceDetailsProvider
        { 
            public static string GetGeoFenceDetailsByGeoFenceID(int geoFenceID)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GeoFenceDetails_GetGeoFenceDetailsByGeoFenceId", true);
                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int16, geoFenceID);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetAllGeoFenceDetailsByUser(string user)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GeoFenceDetails_GetAllByUser", true);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, user);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
    }
}
